package com.example;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class ExcelTest {

    public static String PATH="spring_06_core_aop/src/main/resources/excels/";

    // 生成xlsx文件
    public static void test07xlsx() throws IOException{
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("xlsx_demo_统计表");
            Row row1 = sheet.createRow(0);

            Cell cella1 = row1.createCell(0);

            cella1.setCellValue("Hello");

            Cell cella2 = row1.createCell(1);

            cella2.setCellValue("World 2007 - xlsx ");

            FileOutputStream output = new FileOutputStream(PATH + "xls_demo_统计表.xlsx");

            workbook.write(output);

            output.close();
        }
        System.out.println("xlsx文件生成完成.");

    }
    // 批量写入数据
    public static void bigDateWrite(int n) throws IOException{
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("大数据写入");
        for (int i = 1; i <= n; i++) {
            Row row = sheet.createRow(i-1);
            row.createCell(0).setCellValue("A"+i);
            row.createCell(1).setCellValue("B"+i);
            row.createCell(2).setCellValue("C"+i);
        }
        FileOutputStream stream = new FileOutputStream(PATH+"大数据写入.xlsx");
        workbook.write(stream);
        workbook.close();
        stream.close();
    }
    // 读取数据 到 对象 列表
    public static void read07xlsx() throws IOException{

        FileInputStream fileInputStream = new FileInputStream(PATH + "用户表.xlsx");
        List<User> users = new ArrayList<User>();
        try (Workbook xssfWorkbook = new XSSFWorkbook(fileInputStream)) {
            fileInputStream.close();
            Sheet sheet1 = xssfWorkbook.getSheetAt(0);
            int rowNumber = sheet1.getPhysicalNumberOfRows();
            for (int i = 0; i < rowNumber; i++) {
                Row row = sheet1.getRow(i);
                User user = new User();
                user.setName(row.getCell(0).getStringCellValue());
                user.setAge((int)row.getCell(1).getNumericCellValue());
                user.setMoney(row.getCell(2).getNumericCellValue());
                user.setBirthDate(row.getCell(3).getDateCellValue());
                users.add(user);
            }
            for (User user : users) {
                System.out.println(user.toString());
            }
        }

    }
}
